package com.carl.study.jooq.server;

import com.carl.study.gen.Tables;
import com.carl.study.gen.tables.records.PersonRecord;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.conf.SettingsTools;
import org.jooq.impl.DSL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;

/**
 * @program: FrameStudy
 * @description:
 * @author: Mr.Carl
 **/
public class PersonServer {

    private Connection conn;
    private DSLContext dslContext;

    public PersonServer() {
        // 初始化数据库连接和 DSLContext
        String url = "jdbc:mysql://101.43.4.193:3306/jooq?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root";
        try {
            conn = DriverManager.getConnection(url, user, password);
            dslContext = DSL.using(conn, SQLDialect.MYSQL);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    // 新增
    public void addPerson(String name, String sex, int age, String address) {
        dslContext.insertInto(table("person"))
                .set(field("name"), name)
                .set(field("sex"), sex)
                .set(field("age"), age)
                .set(field("address"), address)
                .execute();
    }

    public void insert(PersonRecord person){

        dslContext.insertInto(Tables.PERSON)
                .values(person.getName(), person.getSex(), person.getAge(), person.getAddress())
                .onDuplicateKeyUpdate()
                .set(field("sex"), person.getSex())
                .set(field("age"), person.getAge())
                .set(field("address"), person.getAddress())
                .execute();
    }

    // 查询
    public Record getPersonByName(String name) {
        return dslContext.select()
                .from(table("person"))
                .where(field("name").eq(name))
                .fetchOne();
    }



    // 更新
    public void updatePerson(String name, String sex, int age, String address) {
        dslContext.update(table("person"))
                .set(field("sex"), sex)
                .set(field("age"), age)
                .set(field("address"), address)
                .where(field("name").eq(name))
                .execute();
    }

    // 删除
    public void deletePerson(String name) {
        dslContext.deleteFrom(table("person"))
                .where(field("name").eq(name))
                .execute();
    }
}
